#encoding: utf-8
=begin

Usage:      This ruby lib provice basic fc4 orders utils, include it as #load 'order-utils.rb'

=end

require 'os'
require 'securerandom'
load 'rds_api.rb'

def merchantCodes
    return @merchantCodes if @merchantCodes
    @merchantCodes = {}
    inq = "select * from crmeb.eb_system_store;"
    res = queryRds inq
    res.each do |r|
      config = JSON.parse r['config']
      @merchantCodes.store r['id'], "M#{r['id']} #{r['name']} 售后 #{config['servicePhone']}"
    end
    return @merchantCodes
end

def productInfo
    return @productInfo if @productInfo
    @productInfo = {}
    queryRds("select id, mer_id, store_name from crmeb.eb_store_product").each do |r|
      @productInfo.store r['id'], {"id"=>r['id'], "name"=>r['name'], "mer_id"=>r['mer_id']}
    end
    return @productInfo
end

def templateList
    return @templateList if @templateList
    @templateList = {}
    inq = "select * from crmeb.eb_shipping_templates;"
    res = queryRds inq
    res.each { |r| @templateList.store r['id'], {"id"=>r['id'], "name"=>r['name']} } #如果用'id':'xxxx',就会出现mer[:id]的样子
    return @templateList
end

def shippingList
    return @shippingList if @shippingList
    @shippingList={}
    inq = "select * from crmeb.eb_shipping_templates;"
    res = queryRds inq
    res.each { |r| @shippingList.store r['id'], "S#{r['id']} #{r['name']}" }
    return @shippingList
end

def gen_pickup_text order

    statusHash = { -1 => '已退款', -2 => '退货成功', 0 => '待发货', 1 => '待收货', 2=> '已收货', 3=> '待评价' }
    refundHash = { 0 => '未退款', 1 => '申请退款中', 2 => '已退款' }

    #add header twice
    #全角空格字符 (　) (_) (﹏)
    order_time = Time.at(order['add_time']).strftime("%y%m%d %H:%M:%S")
    content = "[#{order['line']}]#{sprintf('%05d',order['uid'])}　　　让健康和友善触手可及　　　　1 of ﹏\n"
    content += order['pink_id'] > 0  ? "[拼团订单] #{order['refund_reason_wap']}\n" : "\n"
    content += "#{order['order_code']}　#{order_time}\n"
    if order['shipping_type'] == 1
      uaddr = order['user_address'].chomp().chomp()
      content += "#{uaddr}\n"
    else
      inq = "select name from crmeb.eb_system_store where id = #{order['store_id']};"
      res = queryRds inq
      store_name = res.first ? res.first['name'] : '-----'
      content += "[自提订单] #{store_name}\n" #如果用[xxx]: yyyy，bot 会把这一段儿吞掉
    end
    #content += order['shipping_type'] == 1 ? "#{order['user_address']}\n" : "补拍或自提订单　　　　　　\n"
    content += "#{order['real_name']}    #{order['user_phone']}\n"
    content += order['mark'].to_s == '' ?  "\n" : ":#{order['mark']}\n"
    content += "·　　　　·　　　　　·　　　　　·　　　　　·　2 of ﹏\n"
    content += order['remark'].to_s == '' ? "\n" : ":#{order['remark']}\n"
    content += "#{order['order_code']}　#{order_time}\n"
    content += order['shipping_type'] == 1 ? "#{order['user_address']}\n" : "请与用户其它订单合并，留意左上角用户标识号\n"
    content += "#{order['real_name']}    #{order['user_phone']}\n"
    #content += "-　　　　-　　　　　-　　　　　-　　　　　-　　　　-\n"
    content += "·　　　　·　　　　　·　　　　　·　　　　　·　　　　·\n"
    orderStatus = statusHash[order['status']]
    refundStatus = refundHash[order['refund_status']]
    content += order['status'] == 0 && order['refund_status'] == 0 ? "" : "警告！！#{orderStatus}　#{refundStatus} paid: #{order['paid']}\n"
    content += "数量　　商品名 规格 商户#\n"

    
    merchants={}
    shippings={}
    items = order['items']
    items.each do |item|
        bold = "　"
        bold = " *" if item['cart_info']['cart_num']>1
        bold = "**" if item['cart_info']['cart_num']>2
        qty = sprintf("%d",item['cart_info']['cart_num'])
        suk = item['cart_info']['productInfo']['attrInfo']['suk'] == '默认' ? '' : "##{item['cart_info']['productInfo']['attrInfo']['suk']}"
        merchants.store item['mer_id'], merchantCodes[item['mer_id']]
        shippings.store item['temp_id'], shippingList[item['temp_id']]
        content += "#{bold}#{qty} [　] #{item['cart_info']['productInfo']['store_name']}　#{suk}\n" ##{item['mer_id']}-#{item['temp_id']}\n"
    end

    content += "·　　　　·　　　　　·　　　　　·　　　　　·　　　　·\n"
    content += "　　缺货商品原路退款，品质问题不满意无障碍退换\n"
    content += "　　　　每一天，更安心的选择　FOODTRUST\n"
    merchants.each { |id, descr| content += "#{descr}\n" }
    shippings.each { |id, descr| content += "#{descr}\n" }

=begin #standard line in pospal watcher
    content += "----------------------------------------------------\n"
    content += "-　　　　-　　　　　-　　　　　-　　　　　-　　　　-\n"
    content += "——
    content += "品质问不题题题题题题题题题题题题满意无障碍退换请联系\n"
    content += "123456789012345678901234567890123456789012\n"
=end

    return content

end

def get_merchant_config mer_id
  sql = "select config from crmeb.eb_system_store where id = #{mer_id}"
  config = ''
  queryRds(sql).each do |qr|
    config = qr['config']
  end
  res = {}
  begin
    res = JSON.parse config
  rescue => e
  end
  return res
end

#根据地址分配配送线路
def allocate_line order
  return "T" if order['pink_id'] > 0
  return "X" if order['paid']==0
  return "Z" if order['shipping_type']==2

  if order['order_code'].to_s.include? 'M'
    mer_id = order['order_code'].split('M')[1].split('S')[0].to_i # FC00000215-32M1S1
    config = get_merchant_config mer_id
    list = config['deliveryList'].to_s.gsub(' ','').split('|')
    exceptions = config['deliveryExceptions'].to_s.gsub(' ','').split('|')
    addr = order['user_address'].gsub(' ','')
    list.each do |li|
      if addr.include? li
        exceptions.each { |ex| return 'K' if addr.include? ex }
        return 'G'
      end
    end
  end

  return "K"
end

#数据库类型BigDecimal,在Hash中还是45.13的格式，但转为JSON字符串后它会被转为"0.4513e2"这样的字符串，等待用parseFloat来提取, 很讨厌，待解
def update_pickup_book_for order

  #append cart itmes to records
  items = []
  its = queryRds "select * from crmeb.eb_store_order_cart_info where oid = #{order['id']};"
  its.each do |it| 
    items += [it] 
    #item中的cartinfo是用JSON String保存的，需要转换为正常对象
    items.last.store 'cart_info', JSON.parse(it['cart_info']) #也就是当前刚保存的
    items.last.store 'mer_id', it['mer_id']
    items.last.store 'temp_id', it['cart_info']['productInfo']['temp_id']
  end
  order.store "items", items

  #append line information to records for generate pickup text
  line = allocate_line order
  order.store "line", line
  #update orders information to eb_pickup_book with generating plain text
  text = gen_pickup_text order #line need

  phone = order["user_phone"]
  addr = order['user_address'].gsub(' ','')
  name = order['real_name']
  mark = order['mark'].to_s
  remark = order['remark'].to_s
  if order['shipping_type'] == 2 
      addr = "[共购点自提]##{order['store_id']}号站点"
  end
  code = "UNALLOCATED"
  code = order['order_code'][5,8] if order['order_code']
  time = Time.at(order['add_time']).strftime("%m/%d %H:%M")
  temp_code = '0'
  if order['order_code'] && order['order_code'].split('S')
    temp_code = order['order_code'].split('S')[1]
  end
  
  ship  = "[#{line}#{temp_code}] #{addr}　#{time}\n"
  ship += "　　#{name}　#{phone}　#{code}\n"
  ship += "　#{mark}" if !mark.empty?
  ship += "　::#{remark}\n" if !remark.empty?

  res = queryRds "insert into crmeb.eb_pickup_book ( oid, uid, add_time, update_time, json, text, line, phone, ship, temp_id, order_code ) values (
      #{order['id']}, #{order['uid']}, #{order['add_time']}, #{Time.now.to_i},
      '#{@rds.escape order.to_json}', '#{@rds.escape text}', '#{line}', '#{phone}', '#{ship}', #{temp_code.to_i}, '#{order['order_code']}'
  ) on duplicate key update
    oid = #{order['id']}, uid = #{order['uid']}, add_time = #{order['add_time']}, update_time = #{Time.now.to_i},
    json = '#{@rds.escape order.to_json}', text = '#{@rds.escape text}', line = '#{line}', phone = '#{phone}', ship = '#{ship}', 
    temp_id = #{temp_code.to_i}, order_code = '#{order['order_code']}'
  ;"

  return res

end

def calcTruePriceByOrder order
  oid = order['id']
  cart_items = {}
  queryRds("select cart_id, cart_info from crmeb.eb_store_order_cart_info where oid=#{oid}").each do |r|
    cart_id = r['cart_id']
    cart_info = JSON.parse r['cart_info']
    cart_num = cart_info['cart_num']
    item_stock = cart_info['trueStock'] #已证实item_stock是库存数
    item_name = cart_info['productInfo']['store_name']
    item_price = 0.0
    begin
      item_price += cart_info['truePrice']
    rescue
      item_price += cart_info['truePrice'].to_f #pink单的bug
    end
    item_value = { "cart_id" => cart_id,
                   "cart_num" => cart_num,
                   "item_name" => item_name,
                   "item_price" => item_price
    }
    cart_items.store cart_id, item_value
  end
  total_price = 0.0
  cart_items.each do |id, v|
    #print v['item_name'], ": ", sprintf("%.2f",v['item_price']), " * ", sprintf("%d",v['cart_num']), v['cart_num']>1 ? "<<<\n" : " \n"
    total_price += v["cart_num"] * v["item_price"]
  end
  return total_price
end

def getTemplatePostage order, temp_id
  misc = {}
  begin
    misc = JSON.parse order['misc'].to_s
    tempPostage = misc['tempPosagets'] if !misc['tempPosagets'].nil?
    tempPostage = misc['tempPostages'] if !misc['tempPostages'].nil?
    if tempPostage && tempPostage["#{temp_id}"] #目前的temp_id键值是字符串，返回值有数字零，也有字符串，在StoreOrder Model一侧需要改进
      postage = tempPostage["#{temp_id}"]  
      return postage.class == Float ? postage : postage.to_f
    end
  rescue => e
  end
  return 0
end

def save_fix_sqls sqls, fn
  dir = OS.windows? ? ".\\fix\\" : "./fix/"
  rday =Date.today.strftime('%Y%m%d')
  rtime=Time.now.strftime("%H%M%S")
  File.open(dir + fn + rday + rtime + ".sql","w:UTF-8") { |f| f.write sqls.join("\n")+"\n" }
end

def append_lines_to_fix lines, fn
  dir = OS.windows? ? ".\\fix\\" : "./fix/"
  File.open(dir + fn,"a:UTF-8") { |f| f.write lines.join("\n")+"\n" }
end

def append_line_to_log line, fn
  dir = OS.windows? ? ".\\log\\" : "./log/"
  File.open(dir + fn,"a:UTF-8") { |f| f.write line }
end

def merchantList
  return @merchants if @merchants
  @merchants = {}
  queryRds('select id, name, introduction, tags, sort from crmeb.eb_system_store').each do |q|
    status = q['name'].include?('盘点') ? 'close' : 'open'
    intro = q['introduction']
    intro = q['name'].split('|')[1] if q['name'].include?('|')
    merchant_stat = {"id" => q['id'], "name" => q['name'], "status" => status, "intro" => intro, "tags" => q['tags'], "weight" => q['sort']}
    @merchants.store(q['id'], merchant_stat)
  end
  return @merchants
end

def cateList
  return @cates if @cats
  @cates = {}
  queryRds('select id, cate_name from crmeb.eb_store_category').each do |q|
    cate_stat = {"id" => q['id'], "name" => q['cate_name'] }
    @cates.store(q['id'], cate_stat)
  end
  return @cates
end

def crmeb_stocks mer_id

  puts "reading crmeb stocks..."

  recs = []
  founder = {}
  #在SQL中如果使用format(pav.stock,2)超过3位数结果带逗号，改为cast(value as decimal(12,2)或者convert(value,decimal(12,2))
=begin
    convert(pav.stock,decimal(12,2)) as stock,
    convert(pav.cost,decimal(12,2)) as cost,
    convert(pav.price,decimal(12,2)) as sell_price,
    convert(pav.price,decimal(12,2)) as dis_price,
    convert(pav.price,decimal(12,2)) as vip_price,
=end
  inq = " SELECT
    concat( concat( p.store_name, concat( '[', pav.suk ), ']' ) ) AS product_name,
    p.cate_id as catelog,
    pav.bar_code as bar_code,
    '' as master_code,
    pav.suk as spec,
    '份' as unit,
    pav.stock as stock,
    pav.cost as cost,
    pav.price as sell_price,
    pav.price as dis_price,
    pav.price as vip_price,
    '否' as discount,
    '否' as points,
    10000.00 as max_stock,
    0.00 as min_stock,
    '' as position,
    '' as brand,
    '无' as supplier,
    '' as produce_date,
    180 as warranty,
    '' as pym,
    pav.unique as mcode,
    pav.product_id as scode,
    p.sales as cust1,
    '' as cust2,
    '' as cust3,
    '启用' as status,
    '' as description
  FROM
    crmeb.eb_store_product_attr_value AS pav
    LEFT JOIN crmeb.eb_store_product AS p ON pav.product_id = p.id
  WHERE
    pav.type < 1 and pav.stock > 0 and p.mer_id = #{mer_id};
  "
  res = @rds.query(inq)

  res.each do | r |
    cats = r['catelog'].split(',').uniq
    cat_id = (cats - ['2'])[0]
    r['catelog'] = cateList[cat_id.to_i]['name']
    r['bar_code'] = r['mcode'] if r['bar_code'].to_s.empty?
    recs += [r] 
  end

  return recs
end

def export_merchant_stocks mer_id
  fields = ['product_name','catelog','bar_code','master_code','spec','unit','stock','cost','sell_price','dis_price','vip_price','discount','points','max_stock','min_stock','position','brand','supplier','produce_date','warranty','pym','mcode','scode','cust1','cust2','cust3','status','description']
  tline = "名称（必填）|分类（必填）|条码|主编码|规格|主单位|库存量（必填）|进货价（必填）|销售价（必填）|批发价|会员价|会员折扣|积分商品|库存上限|库存下限|库位|品牌|供货商|生产日期|保质期|拼音码|货号|称编码|自定义1|自定义2|自定义3|商品状态|商品描述"
  headers = tline.split('|')
  puts "save_to_excel..."
  rtime = Time.now.strftime('%y%m%d-%H%M')
  rday = Time.now.strftime('%y%m%d')
  fn1 = OS.windows? ? ".\\nginx\\html\\fc4-stocks\\stocks-M#{mer_id}-#{rtime}.xls" : "./nginx/html/fc4-orders/crmeb-stocks-M#{mer_id}-#{rtime}.xls"
  fn2 = OS.windows? ? ".\\nginx\\html\\fc4-stocks\\stocks-M#{mer_id}-#{rday}.xls" : "./nginx/html/fc4-orders/crmeb-stocks-M#{mer_id}-#{rday}.xls"
  stocks = crmeb_stocks mer_id
  save_to_excel headers, fields, stocks, fn1
  save_to_excel headers, fields, stocks, fn2
end

def mer_id_existed? id
  queryRds("select id from crmeb.eb_system_store where id = #{id}").each { |r| return true }
  return false
end

def create_store id, store, owner, phone, pt
  puts "create store ##{id}"

  config = {
    "deliveryList": "广州市 | 顺德碧桂园",
    "deliveryExceptions": "金穗路 | 龙洞 | 从化 | 增城 | 天源路 | 机务段机山巷 | 东莞庄路 | 白云区 | 黄埔区 | 萝岗区 | 南沙",
    "delvieryContact": owner,
    "deliveryPhone": phone,
    "serviceContact": owner,
    "servicePhone": phone
  }
  sql = "REPLACE INTO `crmeb`.`eb_system_store` ( `id`, `name`, `introduction`, `phone`, `address`, `detailed_address`, `image`, `latitude`, `longitude`, `valid_time`, `day_time`, `add_time`, `is_show`, `is_del`, `config`, is_parallel ) VALUES ( _ID, '_NAME', '每一天，更安心的选择', '_PHONE', '广东,广州,从化', '太平镇高地路太平村蚧三队61号', 'https://oss.foodtrust.cn//de625202004251154513069.png', '23.448022147219564', '113.4889681290712', '1588103410', '00:00:00 - 23:59:59', 0, 1, 0 , '_CONFIG', #{pt});"
  sql = "REPLACE INTO `crmeb`.`eb_system_store` (
    `id`, `name`, `introduction`, `phone`, `address`, `detailed_address`, `image`,
    `latitude`, `longitude`, `valid_time`, `day_time`, `add_time`, `is_show`, `is_del`, `config`,
    `tags`, `wework_id`, `error_msg`, `sort`, `contact`, `wework`,
    `poly_key`, `poly_secret`, `goods_count`, `posts_count`, `follow_count`,
    `store_style`, `is_parallel`, `individuality`, `active_entities`, `cover`, `description`, `favored_kols`,
    `mig_tags`, `favored_locations`, `owner_uid`, `platform_rate`, `openid`, `cate_style`) VALUES (
    _ID, '_NAME', '让真实明白生活', '_PHONE', '广东,广州,天河区', '铭初新群落', 'https://oss.foodtrust.cn//ef3f6202107271822395545.jpg',
    '23.11523362845103', '113.3388742621598', '1588103410', '00:00:00 - 23:59:59', UNIX_TIMESTAMP(), 1, 0, '_CONFIG',
    '', '', '', 0, '咩咩', 'f44c375fdaa4a3b31c7b4819161df7ab',
    '', '', 0, 0, 0,
    0, #{pt}, NULL, '[]', 'https://oss.foodtrust.cn//6bc3d202007271047472318.jpg', '让健康和友善触手可及', '[5,21,22,26,28]',
    'I❤MIG|初生蛋', '[23,24,25]', 0, 0.025, NULL, 0);" if pt.to_i > 0
  sql = sql.gsub('_ID',"#{id}").gsub('_NAME',store).gsub('_PHONE',phone).gsub('_CONFIG',config.to_json);
  queryRds sql

end

def delete_store_owners store_id
  puts "delete store owners for store ##{store_id}"
  sql = "delete from crmeb.eb_system_admin where mer_id = #{store_id};"
  queryRds sql
end

def create_store_owner store_id, phone, owner

  puts "create store owner for store ##{store_id}"
  sql = "INSERT INTO `crmeb`.`eb_system_admin` ( `account`, `pwd`, `real_name`, `roles`, `last_ip`, `last_time`, `add_time`, `login_count`, `level`, `status`, `is_del`, `mer_id` ) VALUES ( '_PHONE', '_PASSWD', '_OWNER', '2', NULL, NULL, 1591234902, 0, 1, 1, 0, _MER_ID );"
  default_passwd = '8dd8503c49b5e8c6aaea1ed7f0c49765' #18898382701 FxxxxS rule
  sql = sql.gsub('_PHONE',phone).gsub('_PASSWD',default_passwd).gsub('_OWNER',owner).gsub('_MER_ID',"#{store_id}");
  queryRds sql

end

#copy shipping templates of source mer_id to target mer_id
def copy_mer_templates source_mid, target_mid

  sqls = []

  #复制模板号
  sql = "insert into crmeb.eb_shipping_templates (name, type, appoint, sort, add_time, mer_id) select name, type, appoint, sort, add_time, #{target_mid} as mer_id from crmeb.eb_shipping_templates where mer_id = #{source_mid};"
  queryRds sql
  sqls += [sql]

  #建立源模板号和新模板号之间的对应关系，这里需要默认模板数量一致
  source_tids = []
  queryRds("select id from crmeb.eb_shipping_templates where mer_id = #{source_mid}").each { |s| source_tids += [s['id']] }
  target_tids = []
  queryRds("select id from crmeb.eb_shipping_templates where mer_id = #{target_mid}").each { |s| target_tids += [s['id']] }

  idx = 0
  source_tids.each do |source_temp_id|

    temp_id = target_tids[idx]
    idx += 1

    #不同的区域分组有不同的uniqid,所以要先把样板模版号下对应的不同uniqid找出来
    queryRds("select uniqid from crmeb.eb_shipping_templates_free where temp_id = #{source_temp_id} group by uniqid;").each do |u|
      uniqid = SecureRandom.hex(7)
      ins = "insert into crmeb.eb_shipping_templates_free (province_id, temp_id, city_id, number, price, type, uniqid )
              select province_id, #{temp_id} as temp_id, city_id, number, price, type, '#{uniqid}' as uniqid
              from crmeb.eb_shipping_templates_free where uniqid = '#{u['uniqid']}';"
      queryRds ins
      sqls += [ins]
    end

    queryRds("select uniqid from crmeb.eb_shipping_templates_region where temp_id = #{source_temp_id} group by uniqid;").each do |u|
      uniqid = SecureRandom.hex(7)
      ins = "insert into crmeb.eb_shipping_templates_region (province_id, temp_id, city_id, `first`, first_price, `continue`, continue_price, type, uniqid )
              select province_id, #{temp_id} as temp_id, city_id, `first`, first_price, `continue`, continue_price, type, '#{uniqid}' as uniqid
              from crmeb.eb_shipping_templates_region where uniqid = '#{u['uniqid']}';"
      queryRds ins
      sqls += [ins]
    end

  end

  return sqls

end

def clear_mer_templates mer_id
  puts "clear shipping templates for store ##{mer_id}"
  sqls = []
  sqls += [ "delete from crmeb.eb_shipping_templates_region where temp_id in ( select id from crmeb.eb_shipping_templates where mer_id = #{mer_id} );" ]
  sqls += [ "delete from crmeb.eb_shipping_templates_free where temp_id in ( select id from crmeb.eb_shipping_templates where mer_id = #{mer_id} );" ]
  sqls += [ "delete from crmeb.eb_shipping_templates where mer_id = #{mer_id};" ]
  commitTrans sqls
end

def create_category id, name
  puts "create attachment category for store ##{id}"
  sql="replace into crmeb.eb_system_attachment_category(id, pid, name) values( #{20+id}, 20, '#{name}' );"
  queryRds sql
end

#创建首页幻灯片模板
def create_group_data id
  puts "create banner templates for store ##{id}"
  fn = OS.windows? ? ".\\store_template\\group_data.sql" : "./store_template/group_data.sql"
  sqls = []
  File.foreach(fn) { |sql| sqls += [sql.gsub("MER_ID", id.to_s).gsub("ADD_TIME",Time.now.to_i.to_s)] }
  commitTrans sqls
end
#(1..7).each { |mer_id| create_group_data mer_id }
#exit
